package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Fachlogik.ExceptionWebShop;
import Fachlogik.Produkt;
import Fachlogik.WebShop;



public class jdbcWebShop  implements WebShop

{
	

	private Connection connection;
	
	 public  jdbcWebShop()throws ExceptionWebShop
	{
		getConnection();
	}

	 public Connection getConnection()throws ExceptionWebShop
	 {
		 try
		 {
			 if (connection == null || connection.isClosed())
			 {
				 
				 String driver = "com.mysql.jdbc.Driver";
				 String url= "jdbc:mysql://localhost:3306/test";
				 String user ="root";
				 String pwd= "";
				 
				 Class.forName(driver);
				 connection = DriverManager.getConnection(url, user, pwd);
				 System.out.println("connection exists");
			 }
		 }
		 catch (SQLException e)
		 {
			 throw new ExceptionWebShop ("Fehler beim Verbinden zur Datenbank");
			 
		 }catch(ClassNotFoundException e)
		 {
			 throw new ExceptionWebShop(" " );
			 
		 }
		 return connection;
	 
	 }
	@Override
	public void speichern(Produkt p) throws ExceptionWebShop
	{
		if (p.getArtikelNr()==  0 )
			insert(p);
		else
			update(p);
		
	}
	
	private void update(Produkt p)
	{
		String sql = "UPDATE schulers SET " +
				"artikelNr = ?, "+
				"art= ?, "+
				"preis = ? " +
				"WHERE id = ?";
		
	}
	
	public void insert(Produkt p) throws ExceptionWebShop
	{
		String sql = "INSERT INTO produkte (artikelnr, art, preis) VALUES "+"(?,?,?)";
		try (PreparedStatement pstmt= getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS))
		{
		
		
			
		pstmt.setInt(1,p.getArtikelNr());
		
		pstmt.setString(2,p.getArt());
		pstmt.setFloat(3, p.getPreis());
		
		pstmt.executeUpdate();
		

		ResultSet generated = pstmt.getGeneratedKeys();
		if(generated.next())
			p.setArtikelNr(generated.getInt (1));
		else
			throw new ExceptionWebShop("Fehler beim Auslesen des Auto-Werts");
		generated.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			throw new ExceptionWebShop ("Fehler beim Speichern");
		}
	
		
	}

	

	@Override
	public List<Produkt> alleProdukts() throws ExceptionWebShop
	{
		List<Produkt> liste= new ArrayList<>();
		String sql ="SELECT id, name, klasse " + 
		" FROM schulers ";
		
		try (PreparedStatement pstmt = getConnection().prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery())
			{
				while (rs.next())
				{
					Produkt s =  new Produkt();
					int artikelNr = rs.getInt("ArtikelNr");
					s.setArt(rs.getString("Art"));
					s.setPreis(rs.getFloat("Preis"));
					liste.add(s);
					
				}
				}catch (SQLException e)
				{
					
					throw new ExceptionWebShop(" Fehler beim Laden der produkt");
				}
		
		
		return liste;
	}

	@Override
	public void beenden(Produkt p) throws ExceptionWebShop
	{
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Produkt> suchen(String text) throws ExceptionWebShop
	{
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void neu(Produkt p) throws ExceptionWebShop
	{
		// TODO Auto-generated method stub
		
	}

	@Override
	public void entfernen(Produkt p) throws ExceptionWebShop
	{
		// TODO Auto-generated method stub
		
	}

	


}

